package jsyf.com.dao;

import com.alibaba.fastjson.JSONObject;
import jsyf.com.entity.TAttachment;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
* @author: @韩武洽 @Wyshown
* @Version: V1.0.0
*/
@Repository
public class TAttachmentDao extends BaseJdbcDao {
    /**
     * FASTDFS_BASEURL
     */
    @Value("${fastdfs.baseurl}")
    public String fastdfsBaseurl;

    /** 系统文件行映射器 */
    private static final TAttachmentRowMapper T_ATTACHMENT_ROW_MAPPER = new TAttachmentRowMapper();

    /**
     * <B>方法名称：</B>插入系统文件<BR>
     * <B>概要说明：</B><BR>
     * @author: @韩武洽 @Wyshown
     * @Version: V1.0.0
     * @param data 系统文件
     * @return int 插入数量
     */
    public int insert(TAttachment data) {
        if (data == null) {
            return 0;
        }
        String sql = " INSERT INTO T_ATTACHMENT(ID, FILE_URL,FILE_NAME, FILE_TYPE, EXTENSION, FILE_SIZE, DATA_GROUP, SORT, REMARK, EXPIRES_TIME,CREATED_BY,DEL_FLAG,CREATED_TIME ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
        Object[] args = new Object[13];
        args[0] = data.getId();
        args[1] = data.getFileUrl();
        args[2] = data.getFileName();
        args[3] = data.getFileType();
        args[4] = data.getExtension();
        args[5] = data.getFileSize();
        args[6] = data.getDataGroup();
        args[7] = data.getSort();
        args[8] = data.getRemark();
        args[9] = data.getExpiresTime();
        args[10] = data.getCreatedBy();
        args[11] = data.getDelFlag();
        args[12] = super.getCurrentTime();
        return super.getJdbcTemplate().update(sql.toString(), args);
    }

    /**
     * <B>方法名称：</B>清除系统文件过期限制<BR>
     * <B>概要说明：</B><BR>
     * @author: @韩武洽 @Wyshown
     * @Version: V1.0.0
     * @param id 文件键值
     * @return int 更新数量
     */
    public int clearExpired(String id) {
        if (StringUtils.isBlank(id)) {
            return 0;
        }
        return super.getJdbcTemplate().update("UPDATE T_ATTACHMENT SET EXPIRES_TIME = NULL,DEL_FLAG= 0 WHERE ID = ?", id);
    }

    /**
     * @param id
     * @param name
     * @Description: 修改方法名和是过期时间  <br/>
     * @return: int
     * @throw:
     * @author: zhangxuan
     * @createTime: 2017/10/27 11:36
     * @Version: V1.0.0
     */
    public int clearExpired(String id,String name) {
        if (StringUtils.isBlank(id)) {
            return 0;
        }
        if(name!=null && ""!=name){
            return  super.getJdbcTemplate().update("UPDATE T_ATTACHMENT SET EXPIRES_TIME = NULL,DEL_FLAG= 0, FILE_NAME =?  WHERE ID = ? ",name, id);
        }else{
            return super.getJdbcTemplate().update("UPDATE T_ATTACHMENT SET EXPIRES_TIME = NULL,DEL_FLAG= 0 WHERE ID = ?", id);
        }
    }

    /**
     * @param id
     * @Description: 更新系统文件过期时间  <br/>
     * @return: int
     * @author: 章玄
     * @createTime: 2017/11/23 14:05
     * @Version: V1.0.0
     */
    public int setExpired(String id) {
        if (StringUtils.isBlank(id)) {
            return 0;
        }
        return super.getJdbcTemplate().update("UPDATE T_ATTACHMENT SET EXPIRES_TIME = NOW(), DEL_FLAG=1 WHERE ID = ?", id);
    }
    
    /**
     * <B>方法名称：</B>更新系统文件过期时间<BR>
     * <B>概要说明：</B><BR>
     * @author: @韩武洽 @Wyshown
     * @Version: V1.0.0
     * @param id 文件键值
     * @param expiresTime 过期时间（null为当前时间）
     * @return int 更新数量
     */
    public int expire(String id, Date expiresTime) {
        if (StringUtils.isBlank(id)) {
            return 0;
        }
        if (expiresTime == null) {
            return super.getJdbcTemplate().update("UPDATE T_ATTACHMENT SET EXPIRES_TIME = NOW(), DEL_FLAG=1 WHERE ID = ?", id);
        }
        return super.getJdbcTemplate().update("UPDATE T_ATTACHMENT SET EXPIRES_TIME = ?, DEL_FLAG=1 WHERE ID = ?", expiresTime, id);
    }

    /**
     * @param
     * @Description: 查询数据库中无效的并且时间比当前时间小两天的数据  <br/>
     * @return: java.util.List<com.alibaba.fastjson.JSONObject>
     * @author: 章玄
     * @createTime: 2017/12/5 14:08
     * @Version: V1.0.0
     */
    public List<JSONObject> findAllByTimeAndStatus(Date date) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ID as id, \n");
        sql.append(" FILE_URL AS fileUrl, \n");
        sql.append(" FILE_NAME AS fileName, \n");
        sql.append(" FILE_TYPE AS fileType, \n");
        sql.append(" EXTENSION AS extension, \n");
        sql.append(" FILE_SIZE AS fileSize,  \n");
        sql.append(" DATA_GROUP AS dataGroup,  \n");
        sql.append(" SORT AS sort, \n");
        sql.append(" REMARK AS remark,  \n");
        sql.append(" EXPIRES_TIME AS expiresTime,  \n");
        sql.append(" CREATED_BY AS createdBy,  \n");
        sql.append(" CREATED_TIME AS createdTime,  \n");
        sql.append(" MODIFY_BY AS modifyBy, \n");
        sql.append(" MODIFY_TIME AS modifyTime,  \n");
        sql.append(" DEL_FLAG AS delFlag  \n");
        sql.append(" FROM T_ATTACHMENT \n");
        sql.append(" WHERE 1=1 ");
        sql.append(" AND DEL_FLAG=1 \n");

        List<Object> sqlArgs = new ArrayList<>();
        if(null != date){
            sql.append(" AND EXPIRES_TIME < ? \n");
            sqlArgs.add(date);
        }
        return super.queryForJsonList(sql.toString(),sqlArgs.toArray());
    }

    /**
     * @Description: 批量删除数据库中的无效数据  <br/>
     * @return: int
     * @author: 章玄
     * @createTime: 2017/12/5 16:09
     * @Version: V1.0.0
     */
    public int deleteBatch(Date date) {
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM T_ATTACHMENT \n");
        sql.append(" WHERE ID IN ( \n");
        sql.append(" SELECT ID FROM ( \n");
        sql.append(" SELECT ID as id FROM T_ATTACHMENT \n");
        sql.append(" WHERE 1=1 AND DEL_FLAG=1 \n");
        List<Object> sqlArgs = new ArrayList<>();
        if(null != date){
            sql.append(" AND EXPIRES_TIME < ? \n");
            sqlArgs.add(date);
        }else{
            return 0;
        }
        sql.append(" )temporary \n");
        sql.append(" ) \n");
        return super.getJdbcTemplate().update(sql.toString(),sqlArgs.toArray());
    }


    /**
     * <B>方法名称：</B>删除系统文件<BR>
     * <B>概要说明：</B><BR>
     * @author: @韩武洽 @Wyshown
     * @Version: V1.0.0
     * @param id 文件键值
     * @return int 删除数量
     */
    public int delete(String id) {
        if (StringUtils.isBlank(id)) {
            return 0;
        }
        return super.getJdbcTemplate().update("DELETE FROM T_ATTACHMENT WHERE ID = ?", id);
    }

    /**
     * <B>方法名称：</B>获取系统文件<BR>
     * <B>概要说明：</B><BR>
     * @param id 文件键值
     * @return TAttachment 系统文件
     * @author: @韩武洽 @Wyshown
     * @Version: V1.0.0
     */
/*    public TAttachment get(String id) {
        if (StringUtils.isBlank(id)) {
            return null;
        }
        List<TAttachment> dataList = getList(id.split(","));
        if (dataList == null || dataList.size() < 1) {
            return null;
        }
        return dataList.get(0);
    }*/

    /**
     * <B>方法名称：</B>获取系统文件列表<BR>
     * <B>概要说明：</B><BR>
     * @author: @韩武洽 @Wyshown
     * @Version: V1.0.0
     * @param keys 文件键值集合
     * @return List<TAttachment> 系统文件列表
     */
 /*   public List<TAttachment> getList(String[] keys) {
        List<Object> args = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ID, FILE_URL, FILE_NAME, FILE_TYPE, EXTENSION, FILE_SIZE, DATA_GROUP, SORT, REMARK, EXPIRES_TIME,CREATED_BY,CREATED_TIME,MODIFY_BY,MODIFY_TIME,DEL_FLAG ");
        sql.append(" FROM T_ATTACHMENT WHERE ID IN ");
        super.appendSqlIn(sql, args, keys);
        sql.append(" ORDER BY FILE_TYPE, FILE_NAME");
        return super.getJdbcTemplate().query(sql.toString(), T_ATTACHMENT_ROW_MAPPER, args.toArray());
    }*/
    
    
    /**
     * <B>方法名称：</B>清空文件缓存方法<BR>
     * <B>概要说明：</B>清空文件缓存方法<BR>
     * @return List<String> 主键KEY集合
     * @author: @韩武洽 @Wyshown
     * @Version: V1.0.0
     */
//    public List<String> clearFileKeys() {

        /*  这里是Oracle的游标的机制(参照游标),未全用正常引用游标
           call的返回值为当前已经过期文件的所有信息 存储过程请看: PKG_SYS.sql
           调用 PKG_SYS包下面的存储过程CLEAR_FILES,输出字段为P_RET_REFCUR
        */
      /*  SimpleJdbcCall call = new SimpleJdbcCall(this.getJdbcTemplate()).
        		withCatalogName("PKG_SYS").withProcedureName("CLEAR_FILES").
                declareParameters(new SqlOutParameter("P_RET_REFCUR", 2));

        		declareParameters(new SqlOutParameter("P_RET_REFCUR", OracleTypes.CURSOR));
      	Map<String, Object> m = call.execute();
      	// 截取操作
      	if(m.values().size() > 0){
          	String temp = m.values().toArray()[0].toString().replace("[", "").replace("]", "");
          	String[] keys = temp.split(",");
          	List<String> ret = new ArrayList<String>();
          	for(String key : keys){
          		String temp1 = key.replace("{", "").replace("}", "");
          		if(!StringUtils.isBlank(temp1)){
          			ret.add(temp1.split("\\=")[1]);
          		}
          	}
          	return ret;
      	}
      	return Collections.emptyList();
    }
    */
}
